﻿Imports System.Data.SqlClient
Public Class FrmSoDuDauKy
    Dim MaHH As String
    Private Sub FrmSoDuDauKy_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        If ThietLap("Thang") <> "" Then
            btnFirst.Enabled = True
            btnNext.Enabled = True
            btnPrev.Enabled = True
            btnLast.Enabled = True
            btnSua.Enabled = True
            btnHuyBo.Enabled = True
            UpdateList()

            If dgvList.Rows.Count = 0 Then
                btnSua.Enabled = False
            End If
        End If
    End Sub

    Private Sub updateValue(ByVal value As String, ByVal key As String, ByVal mycomm As SqlCommand)
        value = value.Trim()
        If value <> "" AndAlso value <> ThietLap(key) Then
            mycomm.Parameters.Clear()
            mycomm.Parameters.AddWithValue("@TuKhoa", key)
            mycomm.Parameters.AddWithValue("@GiaTri", value)
            mycomm.ExecuteNonQuery()
            ThietLap(key) = value
        End If
    End Sub

    Private Sub UpdateList()
        Dim strSql As String = "select ROW_NUMBER() OVER (ORDER BY hh.MaHH) as STT, hh.MaHH as [Mã hàng hóa], TenHH as [Tên hàng hóa], convert(VARCHAR,cast(ct.SLTonDK as money),1) [SL Tồn đầu kỳ], convert(VARCHAR,cast(ct.GTTonDK as money),1) as [Giá trị tồn đầu kỳ], ct.KhoaSo from HangHoa hh "
        strSql &= " inner join CTHangHoa ct on ct.MaHH = hh.MaHH and ct.Thang = @Thang and ct.Nam = @Nam order by STT"
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Thang", ThietLap("Thang"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@Nam", ThietLap("Nam"))
            myAdapter.Fill(myTable)
            For Each row As DataRow In myTable.Rows
                row(3) = CDbl(row(3)).ToString("#,###")
                row(4) = CDbl(row(4)).ToString("#,###")
            Next
            dgvList.DataSource = myTable
            dgvList.Columns.Item(0).Width = 50
            dgvList.Columns.Item(1).Visible = False
            dgvList.Columns.Item(2).Width = 230
            dgvList.Columns.Item(3).Width = 150
            dgvList.Columns.Item(3).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvList.Columns.Item(4).Width = 150
            dgvList.Columns.Item(4).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            dgvList.Columns.Item(5).Visible = False

        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Sub dgvList_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvList.SelectionChanged
        If dgvList.SelectedRows.Count > 0 Then
            If dgvList.CurrentRow.Cells(5).Value = 1 Then
                btnLuu.Enabled = False
                btnSua.Enabled = False
            End If
            MaHH = dgvList.SelectedRows(0).Cells(1).Value.ToString()
            txtTenHH.Text = dgvList.SelectedRows(0).Cells(2).Value.ToString()
            txtSL.Text = dgvList.SelectedRows(0).Cells(3).Value.ToString()
            txtGiaTri.Text = dgvList.SelectedRows(0).Cells(4).Value.ToString()
        End If
    End Sub

    Private Sub btnSua_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSua.Click
        txtSL.ReadOnly = False
        txtGiaTri.ReadOnly = False
        btnLuu.Enabled = True
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        diChuyenChon(1)
    End Sub

    Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
        diChuyenChon(2)
    End Sub

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        diChuyenChon(3)
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        diChuyenChon(4)
    End Sub

    Private Sub diChuyenChon(ByVal kind As Integer)
        If dgvList.Rows.Count > 0 Then
            Dim curIndex As Integer = 0
            If dgvList.CurrentRow IsNot Nothing Then
                curIndex = dgvList.CurrentRow.Index
            End If
            Select Case kind
                Case 1
                    curIndex = 0
                Case 2
                    If curIndex > 0 Then
                        curIndex = curIndex - 1
                    End If
                Case 3
                    If curIndex <> dgvList.Rows.Count - 1 Then
                        curIndex = curIndex + 1
                    End If
                Case 4
                    curIndex = dgvList.Rows.Count - 1
            End Select
            dgvList.CurrentCell = dgvList.Item(0, curIndex)
        End If
    End Sub

    Private Sub btnHuyBo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHuyBo.Click
        HuyBo()
        If dgvList.SelectedRows.Count > 0 Then
            MaHH = dgvList.SelectedRows(0).Cells(1).Value.ToString()
            txtTenHH.Text = dgvList.SelectedRows(0).Cells(2).Value.ToString()
            txtSL.Text = dgvList.SelectedRows(0).Cells(3).Value.ToString()
            txtGiaTri.Text = dgvList.SelectedRows(0).Cells(4).Value.ToString()
        End If
    End Sub

    Private Sub HuyBo()
        txtSL.ReadOnly = True
        txtGiaTri.ReadOnly = True
        btnLuu.Enabled = False
    End Sub

    Private Sub btnLuu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLuu.Click
        If validateInput() Then
            If CapNhat() Then
                MsgBox("Lưu thành công")
                UpdateList()
                HuyBo()
            Else
                MsgBox("Lưu thất bại")
            End If
        End If
    End Sub

    Private Function CapNhat() As Boolean
        Dim strSql As String = ""
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myComm As New SqlCommand
        Dim result As Boolean = True
        Try
            myConn.Open()
            myComm.Connection = myConn
            strSql = "Update CTHangHoa set SLTonDK = @SLTonDK, GTTonDK = @GTTonDK, GiaVonDK = @GiaVonDK where MaHH = @MaHH and Nam = @Nam and Thang = @Thang"
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@Thang", ThietLap("Thang"))
            myComm.Parameters.AddWithValue("@Nam", ThietLap("Nam"))
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@SLTonDK", CDbl(txtSL.Text))
            myComm.Parameters.AddWithValue("@GTTonDK", CDbl(txtGiaTri.Text))
            If CDbl(txtSL.Text) <> 0 Then
                myComm.Parameters.AddWithValue("@GiaVonDK", CDbl(txtGiaTri.Text) / CDbl(txtSL.Text))
            Else
                myComm.Parameters.AddWithValue("@GiaVonDK", 0)
            End If
            myComm.ExecuteNonQuery()
            TinhGiaVonHangBan(MaHH, ThietLap("Thang"), ThietLap("Nam"))
        Catch ex As Exception
            result = False
        End Try
        myConn.Close()
        myConn.Dispose()
        Return result
    End Function

    Public Sub TinhGiaVonHangBan(ByVal MaHH As String, ByVal Thang As Integer, ByVal Nam As Integer)
        Dim myConn As New SqlConnection(chuoiketnoi)
        Dim myTable As New DataTable
        Dim myComm As New SqlCommand
        Dim arlDauKy As New ArrayList()
        Dim strSql As String = "Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = @MaHH and Thang = @Thang and Nam = @Nam "
        Try
            myConn.Open()
            myComm.Connection = myConn
            myComm.CommandText = strSql
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", Thang)
            myComm.Parameters.AddWithValue("@Nam", Nam)
            Dim myAdapter As New SqlDataAdapter(myComm)
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                arlDauKy.Add(myTable.Rows(0)(0).ToString())
                arlDauKy.Add(myTable.Rows(0)(1).ToString())
                arlDauKy.Add(myTable.Rows(0)(2).ToString())
            Else
                arlDauKy.Add(0)
                arlDauKy.Add(0)
                arlDauKy.Add(0)
            End If

            strSql = "Select "
            strSql &= " ct.MaPhieuCT, "
            strSql &= " sc.LoaiCT, "
            strSql &= " ct.SoLuong, "
            strSql &= " case "
            strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
            strSql &= "	else ct.SoLuong * ct.DonGia "
            strSql &= " end as TienHang, "
            strSql &= " ct.ThueNK "
            strSql &= " from"
            strSql &= " CTSoCai ct"
            strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
            strSql &= " where "
            strSql &= " datepart(Month, sc.NgayCT) = @Thang "
            strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
            strSql &= " and ct.MaHH = @MaHH "
            strSql &= " order by sc.NgayCT ASC, sc.LoaiCT"
            myComm.CommandText = strSql
            myComm.Parameters.Clear()
            myComm.Parameters.AddWithValue("@MaHH", MaHH)
            myComm.Parameters.AddWithValue("@Thang", Thang)
            myComm.Parameters.AddWithValue("@Nam", Nam)
            myAdapter = New SqlDataAdapter(myComm)
            myTable = New DataTable()
            myAdapter.Fill(myTable)
            If myTable.Rows.Count > 0 Then
                strSql = "Update CTSoCai set TonKho = @TonKho, GiaTri = @GiaTri, GiaVon = @GiaVon where MaPhieuCT = @MaPhieuCT"
                Dim soluong As Double
                Dim giatri As Double
                Dim row As DataRow = myTable.Rows(0)
                Dim GiaVonTruoc As Double = 0
                myComm.CommandText = strSql
                myComm.Parameters.Clear()
                myComm.Parameters.AddWithValue("@MaPhieuCT", row(0))
                myComm.Parameters.AddWithValue("@TonKho", arlDauKy.Item(0))
                myComm.Parameters.AddWithValue("@GiaTri", arlDauKy.Item(1))
                myComm.Parameters.AddWithValue("@GiaVon", arlDauKy.Item(2))
                myComm.ExecuteNonQuery()
                soluong = arlDauKy.Item(0) + row(2)
                giatri = arlDauKy.Item(1) + row(3)
                If Not row(4) Is DBNull.Value Then
                    giatri += row(4)
                End If
                If soluong <> 0 Then
                    GiaVonTruoc = Math.Round(giatri / soluong)
                Else
                    GiaVonTruoc = 0
                End If
                For i As Integer = 1 To myTable.Rows.Count - 1
                    row = myTable.Rows(i)
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@TonKho", soluong)
                    myComm.Parameters.AddWithValue("@GiaTri", giatri)
                    myComm.Parameters.AddWithValue("@GiaVon", GiaVonTruoc)
                    If row(1) = "PNK" Then
                        soluong = soluong + row(2)
                        giatri = giatri + row(3)
                        If Not row(4) Is DBNull.Value Then
                            giatri += row(4)
                        End If
                    Else
                        soluong = soluong - row(2)
                        giatri = giatri - (row(2) * GiaVonTruoc)
                    End If
                    If soluong <> 0 Then
                        GiaVonTruoc = Math.Round(giatri / soluong)
                    Else
                        GiaVonTruoc = 0
                    End If
                    myComm.Parameters.AddWithValue("@MaPhieuCT", row(0))
                    myComm.ExecuteNonQuery()
                Next

            End If
        Catch ex As Exception
            Throw ex
        End Try
        myConn.Close()
        myConn.Dispose()
    End Sub

    Private Function validateInput() As Boolean
        If Not IsNumeric(Me.txtSL.Text.Trim) Then
            MsgBox("Số lượng phải là kiểu số", MsgBoxStyle.Information)
            txtSL.Focus()
            Return False
        End If

        If Not IsNumeric(Me.txtGiaTri.Text.Trim) Then
            MsgBox("Giá trị phải là kiểu số", MsgBoxStyle.Information)
            txtGiaTri.Focus()
            Return False
        End If
        Return True
    End Function
End Class